/*
 * 
 * Raj Akula	
 * Inventory Look Up Android Application
 * CSC 594
 * 
 * DataHelper
 * Helps add and remove data from database..   
 * 
 */

package nku.csc364.databasehelper;
import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;


public class DataHelper{
	  
		private static final String CATEGORY_TABLE_NAME =  "categorys";
		private static final String PRODUCT_TABLE_NAME = "products";
		private static final String ITEM_TABLE_NAME = "items";
		private static final String SUPPLIER_TABLE_NAME = "suppliers";
		private static final String INVENTORY_TABLE_NAME = "inventory";
	 
	   private Context context;
	   SQLiteDatabase db;
	   private static DataHelper instance;
	   
	   private DataHelper(Context context) {
	      this.context = context;
	      DatabaseHelper openHelper = new DatabaseHelper(this.context);
	      this.db = openHelper.getWritableDatabase();
	      this.db.close();
	   }
	   
	   public static boolean isOpen(){
		   if(instance != null){
		   		return instance.db.isOpen();
		   }
		   else 
			   return true;
	   }

	   
	   public static DataHelper getDataHelper(Context context){
		   if(instance == null)
		   {
			   instance = new DataHelper(context);
		   }
		   return instance;
	   }
	 
	   public Boolean insertCategory(String name, String desc){
		    DatabaseHelper openHelper = new DatabaseHelper(this.context);
		    SQLiteDatabase db = openHelper.getWritableDatabase();
			ContentValues cv = new ContentValues();
			cv.put("name", name);
			cv.put("desc", desc);
			db.insert(CATEGORY_TABLE_NAME, null, cv);
			db.close();
			return true;
		    
	   }

	public Boolean deleteCategory(String name) {
	    DatabaseHelper openHelper = new DatabaseHelper(this.context);
	    SQLiteDatabase db = openHelper.getWritableDatabase();
		Boolean pass =  db.delete(CATEGORY_TABLE_NAME, "name" + "=\"" + name + "\"", null) > 0;
		db.close();
		return pass;
		
	}

	public Boolean insertItem(String product, String supplier, String name,
			String status, float listprice, float unitcost, Integer pict) {
		
	    DatabaseHelper openHelper = new DatabaseHelper(this.context);
	    SQLiteDatabase db = openHelper.getWritableDatabase();
		ContentValues cv = new ContentValues();
		cv.put("product", product);
		cv.put("name", name);
		cv.put("status", status);
		cv.put("listprice", listprice);
		cv.put("unitcost", unitcost);
		cv.put("supplier", supplier);
		cv.put("pict",pict);
		db.insert(ITEM_TABLE_NAME, null, cv);
		
		cv.put("item", name);
		cv.put("qty", 0);
		db.insert(INVENTORY_TABLE_NAME, null, cv);
		
		db.close();
	    return true;
	}

	public Boolean deleteItem (String name) {
	    DatabaseHelper openHelper = new DatabaseHelper(this.context);
	    SQLiteDatabase db = openHelper.getWritableDatabase();
		Boolean deleteInventory = db.delete(INVENTORY_TABLE_NAME, "name" + "=\"" + name + "\"", null) > 0;
		Boolean deleteItem = db.delete(ITEM_TABLE_NAME, "name" + "=\"" + name + "\"", null) > 0;
		db.close();
		return deleteInventory && deleteItem; 
	}

	public Boolean insertProduct(String category, String name, String desc,
			Integer pict) {
		
	    DatabaseHelper openHelper = new DatabaseHelper(this.context);
	    SQLiteDatabase db = openHelper.getWritableDatabase();
		ContentValues cv = new ContentValues();
		cv.put("category", category);
		cv.put("name", name);
		cv.put("desc", desc);
		cv.put("pict", pict);
		db.insert(PRODUCT_TABLE_NAME, null, cv);
		db.close();
	    return true;
		
	}

	public Boolean deleteProduct(String name) {
	    DatabaseHelper openHelper = new DatabaseHelper(this.context);
	    SQLiteDatabase db = openHelper.getWritableDatabase();
		Boolean pass =  db.delete(PRODUCT_TABLE_NAME, "name" + "=\"" + name + "\"", null) > 0;
		db.close();
		return pass;
		
	}

	public Boolean insertSupplier(String name, String status, String addr1,
			String addr2, String city, String state, String zip,
			String phone) {
		
	    DatabaseHelper openHelper = new DatabaseHelper(this.context);
	    SQLiteDatabase db = openHelper.getWritableDatabase();
		ContentValues cv = new ContentValues();
		cv.put("name", name);
		cv.put("status", status);
		db.insert(SUPPLIER_TABLE_NAME, null, cv);
		db.close();
	    return true;
		
	}

	public Boolean deleteSupplier(String name) {
	    DatabaseHelper openHelper = new DatabaseHelper(this.context);
	    SQLiteDatabase db = openHelper.getWritableDatabase();
		Boolean pass =  db.delete(SUPPLIER_TABLE_NAME, "name" + "=\"" + name + "\"", null) > 0;
		db.close();
		return pass;
		
	}

	public Boolean updateQuantity(String itemName, int parseInt) {
		
	    DatabaseHelper openHelper = new DatabaseHelper(this.context);
	    SQLiteDatabase db = openHelper.getWritableDatabase();
		Cursor mCursor = this.getQuantity(itemName);
		Integer qoh = 0;
		String itemName1 = "ERROR";
		for(String x : mCursor.getColumnNames()){
			itemName1 = mCursor.getString(mCursor.getColumnIndexOrThrow(x));
			qoh = (int)mCursor.getLong(mCursor.getColumnIndexOrThrow(x));
		}
		db.delete(INVENTORY_TABLE_NAME, "name" + "=\"" + itemName1 + "\"", null);
		ContentValues cv = new ContentValues();
		cv.put("item", itemName1);
		cv.put("qty", qoh + parseInt);
		db.insert(INVENTORY_TABLE_NAME, null, cv);
		db.close();
		return true;
	}
	
	public List<String> selectAllItems() {
		
	    DatabaseHelper openHelper = new DatabaseHelper(this.context);
	    SQLiteDatabase db = openHelper.getWritableDatabase();
	     List<String> list = new ArrayList<String>();
	     Cursor cursor = db.query(ITEM_TABLE_NAME, new String[] {"name"}, 
	        null, null, null, null, null);
	     if (cursor.moveToFirst()) {
	        do {
	           list.add(cursor.getString(0)); 
	         } while (cursor.moveToNext());
	      }
	     if (cursor != null && !cursor.isClosed()) {
	        cursor.close();
	     }
			db.close();
	     return list;
	}
	
	public List<String> selectAllCategorys() {
		
	    DatabaseHelper openHelper = new DatabaseHelper(this.context);
	    SQLiteDatabase db = openHelper.getWritableDatabase();
	     List<String> list = new ArrayList<String>();
	     Cursor cursor = db.query(CATEGORY_TABLE_NAME, new String[] {"name"}, 
	        null, null, null, null, null);
	     if (cursor.moveToFirst()) {
	        do {
	           list.add(cursor.getString(0)); 
	         } while (cursor.moveToNext());
	      }
	     if (cursor != null && !cursor.isClosed()) {
	        cursor.close();
	     }
			db.close();
	     return list;
	}
	
	public List<String> selectAllProducts() {
		
	    DatabaseHelper openHelper = new DatabaseHelper(this.context);
	    SQLiteDatabase db = openHelper.getWritableDatabase();
	     List<String> list = new ArrayList<String>();
	     Cursor cursor = db.query(PRODUCT_TABLE_NAME, new String[] {"name"}, 
	        null, null, null, null, null);
	     if (cursor.moveToFirst()) {
	        do {
	           list.add(cursor.getString(0)); 
	         } while (cursor.moveToNext());
	      }
	     if (cursor != null && !cursor.isClosed()) {
	        cursor.close();
	     }
			db.close();
	     return list;
	}
	
	public List<String> selectAllSuppliers() {
		
	    DatabaseHelper openHelper = new DatabaseHelper(this.context);
	    SQLiteDatabase db = openHelper.getWritableDatabase();
	     List<String> list = new ArrayList<String>();
	     Cursor cursor = db.query(SUPPLIER_TABLE_NAME, new String[] {"name"}, 
	        null, null, null, null, null);
	     if (cursor.moveToFirst()) {
	        do {
	           list.add(cursor.getString(0)); 
	         } while (cursor.moveToNext());
	      }
	     if (cursor != null && !cursor.isClosed()) {
	        cursor.close();
	     }
			db.close();
	     return list;
	}
	
	public void deleteAll() {
	    DatabaseHelper openHelper = new DatabaseHelper(this.context);
	    SQLiteDatabase db = openHelper.getWritableDatabase();
	      db.delete(ITEM_TABLE_NAME, null, null);
	      db.delete(CATEGORY_TABLE_NAME, null, null);
	      db.delete(PRODUCT_TABLE_NAME, null, null);
	      db.delete(SUPPLIER_TABLE_NAME, null, null);
	      db.delete(INVENTORY_TABLE_NAME, null, null);
			db.close();
	}
	   
	   public Cursor getItem(String name){
		    DatabaseHelper openHelper = new DatabaseHelper(this.context);
		    SQLiteDatabase db = openHelper.getWritableDatabase();
		   Cursor mCursor = db.query(true, ITEM_TABLE_NAME, new String[] {
					"product", "name", "status", "listprice", "unitcost", "supplier", "pict" },
					"name" + "=\"" + name + "\"", null, null, null, null, null);
			if (mCursor != null) {
				mCursor.moveToFirst();	
			}
			db.close();
			return mCursor;
			
	   }
	   
	   public Cursor getCategory(String name){
		    DatabaseHelper openHelper = new DatabaseHelper(this.context);
		    SQLiteDatabase db = openHelper.getWritableDatabase();
		   Cursor mCursor = db.query(true, CATEGORY_TABLE_NAME, new String[] {
					"name", "desc"},
					"name" + "=\"" + name + "\"", null, null, null, null, null);
			if (mCursor != null) {
				mCursor.moveToFirst();	
			}
			db.close();
			return mCursor;
	   }
	   
	   public Cursor getProduct(String name){
		   
		    DatabaseHelper openHelper = new DatabaseHelper(this.context);
		    SQLiteDatabase db = openHelper.getWritableDatabase();
		   Cursor mCursor = db.query(true, PRODUCT_TABLE_NAME, new String[] {
					"name", "desc", "pict"},
					"name" + "=\"" + name + "\"", null, null, null, null, null);
			if (mCursor != null) {
				mCursor.moveToFirst();	
			}
			db.close();
			return mCursor;
	   }
	   
	   public Cursor getSupplier(String name){
		   
		    DatabaseHelper openHelper = new DatabaseHelper(this.context);
		    SQLiteDatabase db = openHelper.getWritableDatabase();
		   Cursor mCursor = db.query(true, SUPPLIER_TABLE_NAME, new String[] {
					"name", "status", "addr1", "addr2", "city", "state", "zip", "phone" },
					"name" + "=\"" + name + "\"", null, null, null, null, null);
			if (mCursor != null) {
				mCursor.moveToFirst();	
			}
			db.close();
			return mCursor;
	   }
	   
	   public Cursor getQuantity(String name){
		   
		    DatabaseHelper openHelper = new DatabaseHelper(this.context);
		    SQLiteDatabase db = openHelper.getWritableDatabase();
		   Cursor mCursor = db.query(true, INVENTORY_TABLE_NAME, new String[] {
					"item", "qty"},
					"item" + "=\"" + name + "\"", null, null, null, null, null);
			if (mCursor != null) {
				mCursor.moveToFirst();	
			}
			db.close();
			return mCursor;
	   }
	
}
